<?php

class PNC_Model extends CI_Model {

	public function __construct() {
		parent::__construct();
	}

	/*
	 * Count Data
	 * @access public
	 * @param string
	 * @param int
	 * @param string
	 * @return int
	 */

	public function count_data($where, $type, $table) {
		$this->db->where($where, $type);
		$this->db->where('rcd_status', 1);
		return $this->db->count_all_results($table);
	}

	public function count_padding_approval($status) {
		$this->db->where('rcd_status', $status);
		return $this->db->count_all_results('pnc_record');
	}

	/* Get number of all approved report */

	public function get_num_summary($rcd_type, $dept) {
		$query;
		if ($rcd_type === 'approved') {
			$query = $this->db
					->select('*')
					->from('pnc_record')
					->join('pnc_employee', 'pnc_employee.staff_id = pnc_record.staff_id')
					->join('pnc_department', 'pnc_department.dept_id = pnc_employee.dept_id')
					->where('dept_alias', $dept)
					->where('rcd_status', 1)
					->count_all_results();
			return $query;
		} else if ($rcd_type === 'pending') {
			$query = $this->db
					->select('*')
					->from('pnc_record')
					->join('pnc_employee', 'pnc_employee.staff_id = pnc_record.staff_id')
					->join('pnc_department', 'pnc_department.dept_id = pnc_employee.dept_id')
					->where('dept_alias', $dept)
					->where('rcd_status', 0)
					->count_all_results();
			return $query;
		}
	}

	public function get_num_group($rcd_type, $group, $dept) {
		$query;
		if ($rcd_type === 'approved') {
			$query = $this->db
					->select('*')
					->from('pnc_record')
					->join('pnc_employee', 'pnc_employee.staff_id = pnc_record.staff_id')
					->join('pnc_department', 'pnc_department.dept_id = pnc_employee.dept_id')
//					->join('pnc_group', 'pnc_employee.grp_id = pnc_group.grp_id')
					->where('dept_alias', $dept)
//					->where('grp_name', $group)
					->where('rcd_status', 1)
					->count_all_results();
			return $query;
		} else if ($rcd_type === 'pending') {
			$query = $this->db
					->select('*')
					->from('pnc_record')
					->join('pnc_employee', 'pnc_employee.staff_id = pnc_record.staff_id')
					->join('pnc_department', 'pnc_department.dept_id = pnc_employee.dept_id')
//					->join('pnc_group', 'pnc_employee.grp_id = pnc_group.grp_id')
					->where('dept_alias', $dept)
//					->where('grp_name', $group)
					->where('rcd_status', 0)
					->count_all_results();
			return $query;
		}
		if ($dept === 'all') {
			if ($rcd_type === 'approved') {
				$query = $this->db
						->select('*')
						->from('pnc_record')
						->join('pnc_employee', 'pnc_employee.staff_id = pnc_record.staff_id')
						->join('pnc_department', 'pnc_department.dept_id = pnc_employee.dept_id')
//						->join('pnc_group', 'pnc_employee.grp_id = pnc_group.grp_id')
//						->where('grp_name', $group)
						->where('rcd_status', 1)
						->count_all_results();
				return $query;
			} else if ($rcd_type === 'pending') {
				$query = $this->db
						->select('*')
						->from('pnc_record')
						->join('pnc_employee', 'pnc_employee.staff_id = pnc_record.staff_id')
						->join('pnc_department', 'pnc_department.dept_id = pnc_employee.dept_id')
						->join('pnc_group', 'pnc_employee.grp_id = pnc_group.grp_id')
//						->where('grp_name', $group)
						->where('rcd_status', 0)
						->count_all_results();
				return $query;
			}
		}
	}

	/*
	 * Delete more datas
	 * @access public
	 * @param string
	 * @param string
	 * @param string
	 */

	public function delete_datas($where, $id, $lea_type, $table) {
		if ($lea_type === 'catchup') { //echo 'PNC_Model ';
		$data = '';
			$subquery = $this->db
					->select('catch_desc')
					->from('pnc_record')
					->join('pnc_cup_des', 'pnc_cup_des.desc_id = pnc_record.catch_desc')
					->where_in('rcd_id', $id)
					->get();
			foreach ($subquery->result_array() as $row) {
				$data = $row['catch_desc'];
			}
//			var_dump($data); die();
			$this->db->where_in('desc_id', $data);
			$this->db->delete('pnc_cup_des');
			$this->db->where_in($where, $id);
			$this->db->delete($table);
			return $this->db->affected_rows() > 0;
		} else if ($lea_type === 'annual' OR $lea_type === '') {
			$this->db->where_in($where, $id);
			$this->db->delete($table);
			return $this->db->affected_rows() > 0;
		}
	}

	/* Delete description of catch up 
	 * @param int
	 * @return array
	 */

//	public function delete_desc_catchup($desc_id) {
//		$this->db->where_in('desc_id', $desc_id);
//		$this->db->delete('pnc_cup_des');
//		return $this->db->affected_rows() > 0;
//	}

	/* Get profile of employee */

	public function get_profile($id) {
		$query = $this
				->db
				->select('*')
				->from('pnc_employee')
				->join('pnc_department', 'pnc_employee.dept_id = pnc_department.dept_id')
				->join('pnc_roles', 'pnc_roles.role_id = pnc_employee.role_id')
				->join('pnc_position', 'pnc_position.pos_id = pnc_employee.pos_id')
//				->join("pnc_group", "pnc_group.grp_id = pnc_employee.grp_id")
				->where('staff_id', $id)
				->where('emp_status', 1)
				->get();
		return $query;
	}

	/* Change New password 
	 * @access public
	 */

	public function change_password($o_pass, $n_pass, $c_pass, $user_id) {
		$data = array(
			'password' => $n_pass
		);
		$this->db->where('password', $o_pass);
		$this->db->where('staff_id', $user_id);
		return $this->db->update('pnc_employee', $data);
	}

	/* Add record when do request */

	public function add_record($staff, $validator, $super_type, $sub_type, $sub_sub, $desc, $reason, $start_date, $end_date, $hour, $day, $work_on, $catch_on) {
		$val_id = '';
		if ($validator) {//Get id of validator's email
			$query = $this->db
					->select('staff_id')
					->from('pnc_employee')
					->where('emp_email', $validator)
					->get();
			if ($query->num_rows() > 0) {
				foreach ($query->result_array() as $row) {
					$val[0] = $row['staff_id']; // Get only one id
				}
				$val_id = $val[0];
			}
		}
		if ($super_type == 1) {
			$data = array(
				'staff_id' => $staff,
				'lea_id' => $super_type,
				'lea_reason' => $reason,
				'start_date' => $start_date,
				'end_date' => $end_date,
				'duration' => $day,
				'hour' => $hour,
				'rcd_status' => 0,
				'val_id' => $val_id,
				'stl_id' => $sub_type,
				'ssl_id' => $sub_sub
			);
			$this->db->insert('pnc_record', $data);
			return $this->db->affected_rows() > 0;
		} else if ($super_type == 2) {
			$catchup_desc = array(
				'description' => $desc,
				'working_on' => $work_on,
				'catch_on' => $catch_on
			);
			$this->db->insert('pnc_cup_des', $catchup_desc);
			$get_cat_id = $this->db->insert_id();
			$data = array(
				'staff_id' => $staff,
				'lea_id' => $super_type,
				'lea_reason' => $reason,
				'start_date' => $start_date,
				'end_date' => $end_date,
				'duration' => $day,
				'hour' => $hour,
				'rcd_status' => 0,
				'val_id' => $val_id,
				'stl_id' => 0,
				'ssl_id' => 0,
				'catch_desc' => $get_cat_id
			);
			$this->db->insert('pnc_record', $data);
			return $this->db->affected_rows() > 0;
		}
	}

	/* Add New Record for Staff */

	public function add_record_staff($staff, $validator, $super_type, $sub_type, $sub_sub, $desc, $reason, $start_date, $end_date, $hour, $day, $work_on, $catch_on) {
		if ($super_type == 1) {
			$data = array(
				'staff_id' => $staff,
				'lea_id' => $super_type,
				'lea_reason' => $reason,
				'start_date' => $start_date,
				'end_date' => $end_date,
				'duration' => $day,
				'hour' => $hour,
				'rcd_status' => 1,
				'val_id' => $validator,
				'stl_id' => $sub_type,
				'ssl_id' => $sub_sub
			);
			$this->db->insert('pnc_record', $data);
			return $this->db->affected_rows() > 0;
		} else if ($super_type == 2) {
			$catchup_desc = array(
				'description' => $desc,
				'working_on' => $work_on,
				'catch_on' => $catch_on
			);
			$this->db->insert('pnc_cup_des', $catchup_desc);
			$get_cat_id = $this->db->insert_id();
			$data = array(
				'staff_id' => $staff,
				'lea_id' => $super_type,
				'lea_reason' => $reason,
				'start_date' => $start_date,
				'end_date' => $end_date,
				'duration' => $day,
				'hour' => $hour,
				'rcd_status' => 1,
				'val_id' => $validator,
				'stl_id' => 0,
				'ssl_id' => 0,
				'catch_desc' => $get_cat_id
			);
			$this->db->insert('pnc_record', $data);
			return $this->db->affected_rows() > 0;
		}
	}

	public function personal_leaving($user_id, $type) {
		if ($type === 'pending') {
			$query = $this->db
					->where('rcd_status', 0)
					->where('staff_id', $user_id)
					->count_all_results('pnc_record');
			return $query;
		}
	}

	/* Show Detail of record leaving request */

	public function show_detail($rcd_id, $lea_type) {
		$query = '';
		if ($lea_type === 'annual') {
			$query = $this->db
					->from('pnc_record')
					->join('pnc_employee', 'pnc_employee.staff_id = pnc_record.staff_id')
					->join('pnc_department', 'pnc_employee.dept_id = pnc_department.dept_id')
//					->join('pnc_group', 'pnc_group.grp_id = pnc_employee.grp_id')
					->join('pnc_roles', 'pnc_roles.role_id = pnc_employee.role_id')
					->join('pnc_position', 'pnc_employee.pos_id = pnc_position.pos_id')
					->join('pnc_leave_type', 'pnc_leave_type.lea_id = pnc_record.lea_id')
					->join('pnc_ss_leave', 'pnc_ss_leave.ssl_id = pnc_record.ssl_id')
					->join('pnc_sub_leave', 'pnc_record.stl_id = pnc_sub_leave.stl_id')
					->where('rcd_id', $rcd_id)
//					->where('rcd_status', 0)
//					->or_where('rcd_status', 1) /* Edit after approved */
					->get();
			return $query;
		} else if ($lea_type === 'catchup') {
			$query = $this->db
					->from('pnc_record')
					->join('pnc_employee', 'pnc_employee.staff_id = pnc_record.staff_id')
					->join('pnc_department', 'pnc_employee.dept_id = pnc_department.dept_id')
//					->join('pnc_group', 'pnc_employee.grp_id = pnc_group.grp_id')
					->join('pnc_leave_type', 'pnc_leave_type.lea_id = pnc_record.lea_id')
					->join('pnc_cup_des', 'pnc_record.catch_desc = pnc_cup_des.desc_id')
					->where('rcd_id', $rcd_id)
//					->where('rcd_status', 0)
//					->or_where('rcd_status', 1) /* Edit after approved */
					->get();
			return $query;
		}
	}

	/* Update annual leaving type */

	public function update_annual_before($record_id, $supper_type, $sub_type, $ss_type, $reason, $start_date, $end_date, $hour, $days) {
		$data = array(
			'lea_id' => $supper_type,
			'stl_id' => $sub_type,
			'ssl_id' => $ss_type,
			'lea_reason' => $reason,
			'start_date' => $start_date,
			'end_date' => $end_date,
			'duration' => $days,
			'hour' => $hour,
			'catch_desc' => NULL
		);
		$this->db->where('rcd_id', $record_id);
		return $this->db->update('pnc_record', $data);
	}

	/* Update catch up leaving type */

	public function update_catchup_before($record_id, $supper_type, $reason, $start_date, $end_date, $hour, $days, $desc_up, $working_on, $catchup_on) {
		$catchup_desc = array(
			'description' => $desc_up,
			'working_on' => $working_on,
			'catch_on' => $catchup_on
		);
		$this->db->insert('pnc_cup_des', $catchup_desc);
		$get_cat_id = $this->db->insert_id();
		$data = array(
			'lea_id' => $supper_type,
			'lea_reason' => $reason,
			'start_date' => $start_date,
			'end_date' => $end_date,
			'duration' => $days,
			'hour' => $hour,
			'stl_id' => 0,
			'ssl_id' => 0,
			'catch_desc' => $get_cat_id
		);
		$this->db->where('rcd_id', $record_id);
		return $this->db->update('pnc_record', $data);
	}

	/* Update catchup leaving if not change leaving type from controller */

	public function update_catchup($record_id, $old_desc_id, $supper_type, $reason, $start_date, $end_date, $hour, $days, $desc_up, $working_on, $catchup_on) {
		$data = array(
			'lea_reason' => $reason,
			'start_date' => $start_date,
			'end_date' => $end_date,
			'duration' => $days,
			'hour' => $hour
		);
		$catch_up = array(
			'description' => $desc_up,
			'working_on' => $working_on,
			'catch_on' => $catchup_on
		);
		$this->db->where('desc_id', $old_desc_id);
		$this->db->update('pnc_cup_des', $catch_up);
		$this->db->where('rcd_id', $record_id);
		return $this->db->update('pnc_record', $data);
	}

	/* Get name of staff to show message when approve success */

	public function get_name_staff($id_staff) {
		$query = $this->db
				->select('*')
				->from('pnc_employee')
				->where('staff_id', $id_staff)
				->get();
		return $query;
	}

}

/* End of file PNC_Model.php */
/* Location: ./application/core/PNC_Model.php */
